﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class ChiTietPhieuTraSachDAO
    {
#region 1. Inserting
        public static bool ThemChiTietPhieuTraSach(ChiTietPhieuTraSachDTO ctpts)
        {
            bool result = false;
            try
            {
                // Tao list sql parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaPT", ctpts.MaPhieuTra));
                sqlParams.Add(new SqlParameter("@MaTaiLieu", ctpts.MaTaiLieu));
                sqlParams.Add(new SqlParameter("@SoNgayTre", ctpts.SoNgayTre));
                sqlParams.Add(new SqlParameter("@TienPhat", ctpts.TienPhat));

                // Goi stored procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_InsertCT_PT", sqlParams);
                if (n == 1)
                {
                    result = true;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
#endregion
#region 2. Sua
        public static bool SuaChiTietPhieuTraSach(ChiTietPhieuTraSachDTO ctpts)
        {
            bool result = false;
            try
            {
                // Tao list sql parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaPT", ctpts.MaPhieuTra));
                sqlParams.Add(new SqlParameter("@MaTaiLieu", ctpts.MaTaiLieu));
                sqlParams.Add(new SqlParameter("@SoNgayTre", ctpts.SoNgayTre));
                sqlParams.Add(new SqlParameter("@TienPhat", ctpts.TienPhat));

                // Goi stored procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_UpdateCT_PT", sqlParams);
                if (n == 1)
                {
                    result = true;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
#region 3. Xoa
        public static bool XoaChiTietPhieuTraSach(String mapt, String matl)
        {
            bool result = false;
            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaPT", mapt));
                sqlParams.Add(new SqlParameter("@MaTaiLieu", matl));

                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_DeleteCT_PT", sqlParams);
                if (n == 1)
                {
                    result = true;
                }

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        //public static bool XoaChiTietPhieuTraSachBangMaPhieuTra(ChiTietPhieuTraSachDTO ctpts)
        //{
        //    bool result = false;
        //    try
        //    {
        //        List<SqlParameter> sqlParams = new List<SqlParameter>();
        //        sqlParams.Add(new SqlParameter("@MaPT", ctpts.MaPhieuTra));

        //        int n = SqlDataAccessHelper.ExecuteNoneQuery("XoaChiTietPhieuTraSachBangMaPhieuTra", sqlParams);
        //        if (n == 1)
        //        {
        //            result = true;
        //        }

        //    }
        //    catch (System.Exception ex)
        //    {
        //        throw ex;
        //    }
        //    return result;
        //}
        //public static bool XoaChiTietPhieuTraSachBangMaTaiLieu(ChiTietPhieuTraSachDTO ctpts)
        //{
        //    bool result = false;
        //    try
        //    {
        //        List<SqlParameter> sqlParams = new List<SqlParameter>();
        //        sqlParams.Add(new SqlParameter("@MaTaiLieu", ctpts.MaTaiLieu));

        //        int n = SqlDataAccessHelper.ExecuteNoneQuery("XoaChiTietPhieuTraSachBangMaTaiLieu", sqlParams);
        //        if (n == 1)
        //        {
        //            result = true;
        //        }

        //    }
        //    catch (System.Exception ex)
        //    {
        //        throw ex;
        //    }
        //    return result;
        //}
#endregion
#region 4. Tim kiem
        public static ChiTietPhieuTraSachDTO TimChiTietPTSByID(String mapt, String matl){
            ChiTietPhieuTraSachDTO result = new ChiTietPhieuTraSachDTO();
            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaPT", mapt));
                sqlParams.Add(new SqlParameter("@MaTaiLieu", matl));

                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectCT_PT", sqlParams);
                DataRow dr = dt.Rows[0];

                result.MaPhieuTra = dr["MaPT"].ToString();
                result.MaTaiLieu = dr["MaTaiLieu"].ToString();
                result.SoNgayTre = int.Parse(dr["SoNgayTre"].ToString());
                result.TienPhat = decimal.Parse(dr["TienPhat"].ToString());
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            return result;
        }
        public static DataTable TimChiTietPTSByPhieuTra(String mapt)
        {
            DataTable result = null;
            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaPT", mapt));

                // goi stored procedure
                result = SqlDataAccessHelper.ExecuteQuery("usp_SelectCT_PTsByAndMaPT", sqlParams);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        public static List<ChiTietPhieuTraSachDTO> TimChiTietPhieuTraSachByTaiLieu(String matl)
        {
            List<ChiTietPhieuTraSachDTO> result = new List<ChiTietPhieuTraSachDTO>();
            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaTaiLieu", matl));

                // goi stored procedure
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectCT_PMsByAndMaTaiLieu", sqlParams);

                foreach (DataRow dr in dt.Rows)
                {
                    ChiTietPhieuTraSachDTO ctpts = new ChiTietPhieuTraSachDTO();
                    ctpts.MaPhieuTra = dr["MaPT"].ToString();
                    ctpts.MaTaiLieu = dr["MaTaiLieu"].ToString();
                    ctpts.SoNgayTre = int.Parse(dr["SoNgayTre"].ToString());
                    ctpts.TienPhat = decimal.Parse(dr["TienPhat"].ToString());
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        public static DateTime TimNgayMuonCuaTaiLieu(String matl){
            DateTime result = new DateTime();
            try
            {
                SqlDataAccessHelper.ConnectionString = SqlDataAccessHelper.ReadConnectionString("ConnectionString.xml");
                SqlConnection connect = new SqlConnection(SqlDataAccessHelper.ConnectionString);
                connect.Open();
                SqlCommand command = connect.CreateCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "TimNgayMuonCuaTaiLieu";
                command.Parameters.Add(new SqlParameter("@MaTaiLieu", matl));
                SqlParameter outparam = new SqlParameter();
                outparam.ParameterName = "@ngaymuon";
                outparam.DbType = DbType.DateTime;
                outparam.Direction = ParameterDirection.Output;
                command.Parameters.Add(outparam);

                command.UpdatedRowSource = UpdateRowSource.OutputParameters;
                command.ExecuteNonQuery();
                result = DateTime.Parse(command.Parameters["@ngaymuon"].Value.ToString());
                connect.Close();
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            return result;
        }
        public static int TinhSoNgayTreCuaTaiLieu(String matl){
            int result = 0;
            try
            {
                DateTime ngaymuon = TimNgayMuonCuaTaiLieu(matl);
                result = (int)TimeSpan.Parse(DateTime.Now.Subtract(ngaymuon).ToString()).TotalDays;
                if (result < 0)
                {
                    result = 0;
                }
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            return result;
        }
        public static bool CheckChiTietPhieuTraSach(String mapt, String matl)
        {
            bool result = false;
            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaPT", mapt));
                sqlParams.Add(new SqlParameter("@MaTaiLieu", matl));

                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectCT_PT", sqlParams);
                if (dt.Rows.Count > 0)
                {
                    result = true;
                }
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            return result;
        }
#endregion
    }
}
